﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Utils;
using Settings;

namespace PanasonicWarranty.DAL
{
    public partial class DBSysUser
    {
        public static bool CheckExistUser(string userName)
        {
            using (SqlConnection conn = new SqlConnection(Settings.CommonSetting.ConnectionString))
            {
                conn.Open();
                string query = string.Format(@"
                IF EXISTS(
		            SELECT NULL AS [EMPTY]
		            FROM SysUser
                    WHERE UserName = @UserName
	            ) 
	            SET @RetValue = 1
	            ELSE 
	            SET @RetValue = 0
                ", userName);
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.Add("@RetValue", SqlDbType.Bit).Direction = ParameterDirection.Output;
                    cmd.Parameters.AddWithValue("@UserName", userName);
                    cmd.ExecuteNonQuery();
                    return (bool)cmd.Parameters["@RetValue"].Value;
                }
            }
        }

        /// <summary>
        /// Lấy ra danh sách group chứa user
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static SqlDataReader UserInGroup(int userID)
        {
            string query = string.Format(@"
                SELECT sg.*
                FROM SysGroup sg INNER JOIN SysUserGroup sug ON sg.GroupID = sug.GroupID
                WHERE sg.[Disabled] = 0
	                AND sug.UserID = {0}", userID);
            return SqlHelper.ExecuteReader(CommonSetting.ConnectionString, CommandType.Text, query);
        }

        public static SqlDataReader Login(string userName, string password)
        {
            string query = "SELECT * FROM SysUser WHERE UserName = @UserName AND Password = @Password";
            return SqlHelper.ExecuteReader(CommonSetting.ConnectionString, CommandType.Text, query,
                                                new SqlParameter("@UserName", userName),
                                                new SqlParameter("@Password", password));
        }

        /// <summary>
        /// Lấy danh sách user để phân nhóm.
        /// </summary>
        /// <param name="GroupID"></param>
        /// <returns></returns>
        public static SqlDataReader GetAllToSetGroup(int GroupID)
        {
            try
            {
                string query = @"
                    SELECT su.*, CAST(1 AS BIT) Selected FROM SysUser su 
	                WHERE su.UserID IN (SELECT UserID FROM SysUserGroup WHERE GroupID = @GroupID)
	                UNION ALL
	                SELECT su.*, CAST(0 AS bit) Selected FROM SysUser su 
	                WHERE su.UserID NOT IN (SELECT UserID FROM SysUserGroup WHERE GroupID = @GroupID)
	                ORDER BY UserName";
                return SqlHelper.ExecuteReader(CommonSetting.ConnectionString, CommandType.Text, query, new SqlParameter("@GroupID", GroupID));
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public static bool ResetPassword(int UserID, string password)
        {
            try
            {
                string query = "UPDATE SysUser SET Password = @Password WHERE UserID = @UserID";
                int result = SqlHelper.ExecuteNonQuery(CommonSetting.ConnectionString, CommandType.Text, query,
                                                       new SqlParameter("@Password", UserID),
                                                       new SqlParameter("@UserID", password));
                return (result > 0);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
